package com.yuanqi.repository;

import com.yuanqi.domain.InventoryTransaction;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 库存交易数据访问层
 */
@Repository
public interface InventoryTransactionRepository extends JpaRepository<InventoryTransaction, Long> {

    /**
     * 根据SKU ID查找库存交易记录
     */
    List<InventoryTransaction> findBySkuIdOrderByCreatedAtDesc(Long skuId);

    /**
     * 根据仓库ID查找库存交易记录
     */
    List<InventoryTransaction> findByWarehouseIdOrderByCreatedAtDesc(Long warehouseId);

    /**
     * 根据库位ID查找库存交易记录
     */
    List<InventoryTransaction> findByLocationIdOrderByCreatedAtDesc(Long locationId);

    /**
     * 根据事务类型查找库存交易记录
     */
    List<InventoryTransaction> findByTxnTypeOrderByCreatedAtDesc(String txnType);

    /**
     * 根据参考表查找库存交易记录
     */
    List<InventoryTransaction> findByReferenceTableOrderByCreatedAtDesc(String referenceTable);

    /**
     * 根据参考ID查找库存交易记录
     */
    List<InventoryTransaction> findByReferenceIdOrderByCreatedAtDesc(Long referenceId);

    /**
     * 根据日期范围查找库存交易记录
     */
    @Query("SELECT it FROM InventoryTransaction it WHERE it.createdAt BETWEEN :startDate AND :endDate ORDER BY it.createdAt DESC")
    List<InventoryTransaction> findByCreatedAtBetween(@Param("startDate") LocalDateTime startDate,
                                                      @Param("endDate") LocalDateTime endDate);

    /**
     * 查找SKU的最新库存交易记录
     */
    @Query("SELECT it FROM InventoryTransaction it WHERE it.skuId = :skuId ORDER BY it.createdAt DESC")
    List<InventoryTransaction> findLatestBySkuId(@Param("skuId") Long skuId);

    /**
     * 统计SKU入库数量
     */
    @Query("SELECT COALESCE(SUM(it.qty), 0) FROM InventoryTransaction it WHERE it.skuId = :skuId AND it.qty > 0")
    BigDecimal sumInQuantityBySkuId(@Param("skuId") Long skuId);

    /**
     * 统计SKU出库数量
     */
    @Query("SELECT COALESCE(SUM(ABS(it.qty)), 0) FROM InventoryTransaction it WHERE it.skuId = :skuId AND it.qty < 0")
    BigDecimal sumOutQuantityBySkuId(@Param("skuId") Long skuId);

    /**
     * 统计今日库存交易数量
     */
    @Query("SELECT COUNT(it) FROM InventoryTransaction it WHERE DATE(it.createdAt) = CURRENT_DATE")
    Long countTodayTransactions();

    /**
     * 统计本月库存交易数量
     */
    @Query("SELECT COUNT(it) FROM InventoryTransaction it WHERE YEAR(it.createdAt) = YEAR(CURRENT_DATE) AND MONTH(it.createdAt) = MONTH(CURRENT_DATE)")
    Long countMonthTransactions();

    /**
     * 根据事务编码查找
     */
    InventoryTransaction findByTxnCode(String txnCode);
}
